const db = require( '../db/db' );
const wordUtils = require( '../utils/word_utils' );

const tableName = 'app.data_insight';

const insert = async function ( params ) {

    var titleFti = wordUtils.segmentAndJoin( params.title, ' ' );

    var sql = `insert into ${ tableName } (uid, user_id, title, description, content, title_fti, thumb, create_at, update_at) values($1, $2, $3, $4, $5, to_tsvector('simple', $6), $7, now(), now())`;
    var paramArray = [ params.uid, params.userId, params.title, params.desc, params.content, titleFti, params.thumb ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '新建data_insights记录出错', e );
    }

}

const update = async function ( params ) {

    var titleFti = wordUtils.segmentAndJoin( params.title, ' ' );

    var sql = `update ${ tableName } set title=$1, description=$2, content=$3, title_fti=to_tsvector('simple', $4), share=$5, update_at=now() where uid=$6 and user_id=$7`;
    var paramArray = [ params.title, params.desc, params.content, titleFti, params.share, params.uid, params.userId ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '新建data_insights记录出错', e );
    }
}

const updateThumb = async function ( params ) {
    var sql = `update ${ tableName } set thumb=$1, update_at=now() where uid=$2 and user_id=$3`;
    var paramArray = [ params.thumb, params.uid, params.userId ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '保存data_insights缩略图出错', e );
    }
}

const remove = async function ( params ) {
    var sql = `delete from ${ tableName } where uid=$1 and user_id=$2`;
    var paramArray = [ params.uid, params.userId ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '删除data_insights记录出错', e );
    }

}

const list = async function ( params ) {

    let orderBy = params.orderBy;
    let order = params.order;

    if ( orderBy !== 'update_at' && orderBy !== 'visited' ) {
        orderBy = 'update_at';
    }

    if ( order !== 'desc' && order !== 'asc' ) {
        order = 'desc';
    }

    var sql = `select uid, title, description, update_at, share from ${ tableName } where user_id=$1 order by ${ orderBy } ${ order } offset $2 limit $3`;
    var paramArray = [ params.userId, params.offset, params.limit ];

    if ( params.search ) {
        var searchFti = wordUtils.segmentAndJoin( params.search, ' & ' );
        var sql = `select uid, title, description, update_at, share from ${ tableName } where user_id=$1 and (title_fti @@ to_tsquery('simple', $2) or desc_fti @@ to_tsquery('simple', $3)) order by ${ orderBy } ${ order } offset $4 limit $5`;
        paramArray = [ params.userId, searchFti, searchFti, params.offset, params.limit ];
    }

    try {
        var result = await db.runSql( sql, paramArray );

        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '查询data_insights记录出错', e );
    }

    return null;

}

const thumb = async function ( params ) {
    var sql = `select thumb from ${ tableName } where uid=$1`;
    var paramArray = [ params.uid ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '查询data_insights缩略图出错', e );
    }
}

const get = async function ( params ) {
    var sql = `select uid, title, description, share, content, update_at from ${ tableName } where uid=$1 and user_id=$2 limit 1`;
    var paramArray = [ params.uid, params.userId ];

    try {
        var result = await db.runSql( sql, paramArray );
        if ( result ) {
            return result.rows;
        }
        return null;
    } catch ( e ) {
        console.log( '查询项目详细信息出错', e );
    }
}

exports.insert = insert;
exports.update = update;
exports.updateThumb = updateThumb;
exports.remove = remove;
exports.list = list;
exports.thumb = thumb;
exports.get = get;
